package cn.umakr.dao.Impl;

import cn.umakr.dao.DeptDao;
import cn.umakr.entity.Dept;
import cn.umakr.util.DbHelper;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by wangbo on 2017/7/17.
 */

public class DeptDaoImpl implements DeptDao {

    //实例化一个QueryRunner对象用于执行sql语句，执行sql语句时需要提供Connection对象。
    private QueryRunner queryRunner = new QueryRunner();

    /**
     * 增加一个Dept对象
     *
     * @param dept
     */
    @Override
    public Integer save(Dept dept) {
        Integer ststus = 0;
        try {
            String sql = "insert into tab_dept values(null,?,?,?,?)";
            Connection connection = DbHelper.getConn();
            ststus = queryRunner.update(connection, sql, dept.getDeptName(), dept.getRegDate(), dept.getDeptDiscripte
                    (), dept.getDeptManage());
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ststus;
    }

    /**
     * 删除一个Dept对象,根据主键删除
     *
     * @param id
     */
    @Override
    public Integer delete(Integer id) {

        Integer status = 0;
        try {
            String sql = "delete from tab_dept where id = ?";
            Connection connection = DbHelper.getConn();
            status = queryRunner.update(connection, sql, id);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return status;
    }

    /**
     * 修改Dept对象
     *
     * @param dept
     */
    @Override
    public Integer update(Dept dept) {

        Integer status = 0;
        try {
            String sql = "update tab_dept set deptName = ? ,regDate = ? , deptDiscripte = ?,deptManage = ? where id =" +
                    " ?";
            Connection connection = DbHelper.getConn();
            status = queryRunner.update(connection, sql, dept.getDeptName(), dept.getRegDate(), dept.getDeptDiscripte
                    (), dept.getDeptManage(), dept.getId());
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return status;
    }

    /**
     * 查询所有Dept对象
     */
    @Override
    public List<Dept> findAll() {

        List<Dept> depts = new ArrayList<Dept>();
        try {
            String sql = "select * from tab_dept";
            BeanListHandler<Dept> rsh = new BeanListHandler<Dept>(Dept.class);
            Connection connection = DbHelper.getConn();
            depts = queryRunner.query(connection, sql, rsh);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return depts;
    }

    /**
     * 根据主键查找Dept对象
     *
     * @param id
     */
    @Override
    public Dept findById(Integer id) {

        Dept dept = new Dept();
        try {
            String sql = "select * from tab_dept where id = ?";
            BeanHandler<Dept> rsh = new BeanHandler<Dept>(Dept.class);
            Connection connection = DbHelper.getConn();
            dept = queryRunner.query(connection, sql, rsh, id);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dept;
    }

    /**
     * 根据属性查找Dept对象
     *
     * @param propName
     * @param propValue
     */
    @Override
    public List<Dept> findByProperty(String propName, Object propValue) {

        List<Dept> depts = new ArrayList<Dept>();
        try {
            String sql = "select * from where" + propName + "=?";
            BeanListHandler<Dept> rsh = new BeanListHandler<Dept>(Dept.class);
            Connection connection = DbHelper.getConn();
            depts = queryRunner.query(connection, sql, rsh, propValue);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return depts;
    }

    /**
     * 统计用户总数
     *
     * @return
     */
    @Override
    public Integer countDept() {
        Integer count = 0;
        try {
            String sql = "select count(*) from tab_dept";
            ScalarHandler rsh = new ScalarHandler();
            Connection connection = DbHelper.getConn();
            count = ((Long) queryRunner.query(connection, sql, rsh)).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 分页查询部门数据
     *
     * @param currentPage 当前页码
     * @param pageSize    每页大小
     * @return
     */
    @Override
    public List<Dept> findByPage(Integer currentPage, Integer pageSize) {
        List<Dept> depts = new ArrayList<Dept>();
        try {
            String sql = "select * from tab_dept limit "
                    + (currentPage - 1) * pageSize + "," + pageSize;
            BeanListHandler<Dept> rsh = new BeanListHandler<Dept>(Dept.class);
            Connection connection = DbHelper.getConn();
            depts = queryRunner.query(connection, sql, rsh);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return depts;
    }
}
